SELECT
b.billing_date AS "Date",
IF(bm10.billing_code IS NULL AND bm11.billing_code IS NULL, 'Missing BOTH 98010 and 98011',
IF(bm10.billing_code IS NULL, 'Missing 98010 but 98011 was billed',
IF(bm11.billing_code IS NULL, 'Missing 98011 but 98010 was billed', 'OK'))) AS billing_status,
bm.billing_code AS "LFP Code Billed"
FROM
billingmaster bm
JOIN billing b ON b.billing_no = bm.billing_no
JOIN demographic demo ON demo.demographic_no = bm.demographic_no
LEFT JOIN billingmaster bm10 ON bm10.service_date = bm.service_date AND bm10.billing_code = 98010
LEFT JOIN billingmaster bm11 ON bm11.service_date = bm.service_date AND bm11.billing_code = 98011
WHERE
b.provider_no = '{provider_no}'
AND bm.billing_code IN (98031,98032,98033,98030,98034,98022,98021,98020)
AND demo.patient_status = 'AC'
AND bm.service_date >= '{start_date}'
AND b.billing_date >= '{start_date}'
AND NOT (bm10.billing_code IS NOT NULL AND bm11.billing_code IS NOT NULL)
GROUP BY
b.billing_date
HAVING
billing_status <> 'OK'
ORDER BY
b.billing_date;
SELECT
provider_no,
CONCAT(last_name, ', ', first_name, ' (', provider_no, ')') AS provider
FROM
provider
WHERE
status = '1'
AND provider_type = 'doctor'
AND ohip_no > 1
ORDER BY
provider_no;